<?php
declare (strict_types = 1);

namespace app\admin\service\data\course;

use app\admin\model\data\Classes as ClassesStatistic;
use app\admin\model\data\Course as CourseStatistic;
use app\admin\model\data\Live as LiveStatistic;
use app\admin\model\data\LiveSmall as LiveSmallStatistic;
use app\admin\model\data\PaymentOrderItem;
use app\admin\model\data\Video as VideoStatistic;
use app\admin\model\data\Visit;
use app\admin\service\data\DataService;
use app\course\model\base\admin\Course as CourseModel;
use think\facade\Request;

/**
 * 课程统计
 */
class Course extends DataService
{

    /**
     * 课程概况
     * Author   Martinsun<syh@sunyonghong.com>
     * Date 2020-07-22
     * @return   [type]                         [description]
     */
    public function survey()
    {
        // 在售课程
        $data['total'] = $this->getCourseCount(CourseStatistic::class);
        // 点播课程
        $data['video'] = $this->getCourseCount(VideoStatistic::class);
        // 大班课
        $data['live'] = $this->getCourseCount(LiveStatistic::class);
        // 小班课
        $data['live_small'] = $this->getCourseCount(LiveSmallStatistic::class);
        // 班级课
        $data['classes'] = $this->getCourseCount(ClassesStatistic::class);

        return $this->parseToData($data, 1, '获取成功');
    }

    /**
     * 获取课程数量
     * Author   Martinsun<syh@sunyonghong.com>
     * Date 2020-07-22
     * @param    [type]                         $course [description]
     * @return   [type]                                 [description]
     */
    protected function getCourseCount($course)
    {
        return app($course)->scope('mhm')->where('status', 1)->where('listing_time', '<=', Request::time())->count();
    }
    /**
     * 课程榜
     * Author   Martinsun<syh@sunyonghong.com>
     * Date 2020-07-17
     * @param    string                         $value [description]
     * @return   [type]                                [description]
     */
    public function rank()
    {
        $time = $this->parseTime();
        $data = [];
        // 今日访问课程榜单 TOP5
        $course_top5 = Visit::scope('mhm')->whereBetween('visit_time', $time)->whereIn('resource_type', ['video', 'classes', 'live', 'live_small'])->group('resource_type,resource_id')->field(['COUNT(DISTINCT ip)' => 'tp_uv', 'resource_type', 'resource_id'])->order('tp_uv', 'desc')->limit(5)->select()->each(function ($item) use ($time) {
            $course                 = CourseModel::where('id', $item->resource_id)->findOrEmpty();
            $item->title            = $course->getAttr('title');
            $item->course_type_text = $course->getAttr('course_type_text');
            $item->visit_count      = $item->tp_uv;
            $product_type_id        = Visit::getProductType($item->resource_type);
            // 当前课程下单量
            $item->place_count = PaymentOrderItem::where('product_type_id', $product_type_id)->where('product_id', $item->resource_id)->whereBetween('create_time', $time)->count();
            // 当前课程下单人数
            $place_user_count       = PaymentOrderItem::where('product_type_id', $product_type_id)->where('product_id', $item->resource_id)->whereBetween('create_time', $time)->value('count(DISTINCT user_id)');
            $item->place_conversion = $this->getProportionRate($item->visit_count, $place_user_count);
            $item->place_user_count = $place_user_count;

        });

        $data['visit_rank'] = $course_top5->toArray();
        // 支付榜单--TOP5
        $top5 = PaymentOrderItem::scope('mhm','__TABLE__.mhm_id')->field([
            'product_type_id',
            'product_id',
            'SUM(payment_order_item.payment)'            => 'tp_payment',
            'COUNT(DISTINCT payment_order_item.user_id)' => 'payment_user_count',
        ])->withJoin(['pdorder' => function ($query) {
            $query->withField(['status', 'payment_time']);
        }])->whereIn('pdorder.status', [20, 30, 40])->whereIn('product_type_id', '1,2,3,4')->group('product_type_id,product_id')->order('payment_user_count', 'desc')->limit(5)->select()->each(function ($item) {
            $item->title   = CourseModel::where('id', $item->product_id)->value('title');
            $item->payment = floatval($item->tp_payment);
        });
        $data['pay_rank'] = $top5->toArray();
        $data['is_platform'] = request()->is_platform;
        return $this->parseToData($data, 1, '获取成功');

    }

    /**
     * 访问排行列表 -- 带分页
     * Author   Martinsun<syh@sunyonghong.com>
     * Date 2020-07-23
     * @return   [type]                         [description]
     */
    public function visitRankList()
    {
        $resource_type = ['video', 'live', 'live_small', 'classes'];
        $timespan      = $this->parseTime();
        $data          = Visit::getVisitRankPageList($resource_type, $timespan);
        $data->each(function ($item) use ($timespan) {
            // 课程名称
            $course                 = CourseModel::with(['cover_url'])->where('id', $item->resource_id)->findOrEmpty();
            $item->title            = $course->getAttr('title');
            $item->cover_url        = $course->getAttr('cover_url');
            $item->course_type_text = $course->getAttr('course_type_text');
            $product_type_id        = Visit::getProductType($item->resource_type);
            // 当前课程下单量
            $item->place_count = PaymentOrderItem::where('product_type_id', $product_type_id)->where('product_id', $item->resource_id)->whereBetween('create_time', $timespan)->count();
            // 当前课程下单中,已经支付订单数
            $payment = PaymentOrderItem::field([
                'COUNT(1)'                        => 'place_payment_count',
                'SUM(payment_order_item.payment)' => 'payment_total',
            ])->withJoin([
                'pdorder' => ['status'],
            ])->where('product_type_id', $product_type_id)
                ->where('product_id', $item->resource_id)
                ->whereBetween('__TABLE__.create_time', $timespan)
                ->whereIn('pdorder.status', [20, 30, 40])->find();
            $item->place_payment_count = $payment['place_payment_count'];
            // 当前课程下单中,已经支付的金额
            $item->payment_total = floatval($payment['payment_total']);
            // 当前课程下单人数
            $place_user_count = PaymentOrderItem::where('product_type_id', $product_type_id)->where('product_id', $item->resource_id)->whereBetween('create_time', $timespan)->value('count(DISTINCT user_id)');
            // 下单转化率 = 下单人数 / 访问用户数 /
            $item->place_conversion = $this->getProportionRate($item->tp_uv, $place_user_count, 0);
            // 支付转化率 = 下单总数 / 已支付订单数
            $item->payment_conversion = $this->getProportionRate($item->place_count, $item->place_payment_count, 0);

            $item->place_user_count = $place_user_count;
        });
        $data['is_platform'] = request()->is_platform;

        return $this->parseToData($data, 1, '获取成功');
    }

    /**
     * 某个课程的详情分析
     * Author   Martinsun<syh@sunyonghong.com>
     * Date 2020-07-26
     * @return   [type]                         [description]
     */
    public function details($course_id)
    {
        // 查询课程信息
        $course = CourseModel::where('id', $course_id)->field(['id', 'course_type', 'course_live_type', 'title'])->findOrEmpty();
        $data   = ['title' => $course->title];
        if (!$course->isEmpty()) {
            // 解析时间
            $daterange = $this->parseSelectDaterange();
            // 取得课程对应的产品类型
            $resource_type = CourseModel::getCourseResourceType($course);
            $visit         = Visit::getResourceVisit($resource_type, $course_id, $daterange['timespan']);
            // 课程PV量
            $data['pv'] = $visit['tp_pv'];
            // 课程UV
            $data['uv']      = $visit['tp_uv'];
            $product_type_id = Visit::getProductType($resource_type);
            // 下单人数
            $place_user_count = PaymentOrderItem::where('product_type_id', $product_type_id)->where('product_id', $course_id)->whereBetween('create_time', $daterange['timespan'])->value('count(DISTINCT user_id)');
            // 下单转化率 = 下单人数 / 访问用户数
            $data['place_conversion'] = $this->getProportionRate($data['uv'], $place_user_count, 0);
            // 当前课程下单量
            $data['place_count'] = PaymentOrderItem::where('product_type_id', $product_type_id)->where('product_id', $course_id)->whereBetween('create_time', $daterange['timespan'])->count();
            // 当前课程下单中,已经支付订单数
            $payment = PaymentOrderItem::field([
                'COUNT(1)'                        => 'place_payment_count',
                'SUM(payment_order_item.payment)' => 'payment_total',
            ])->withJoin([
                'pdorder' => ['status'],
            ])->where('product_type_id', $product_type_id)
                ->where('product_id', $course_id)
                ->whereBetween('__TABLE__.create_time', $daterange['timespan'])
                ->whereIn('pdorder.status', [20, 30, 40])->find();
            $data['place_payment_count'] = $payment['place_payment_count'];
            // 当前课程下单中,已经支付的金额
            $data['payment_total'] = floatval($payment['payment_total']);

            // 支付转化率 = 已支付订单数 / 下单总数
            $data['payment_conversion'] = $this->getProportionRate($data['place_count'], $data['place_payment_count'], 0);
            // 图表数据
            $rows = $this->parseRowsDate($daterange['rows']);
            // 统计图表
            if ($daterange['days'] == 1) {
                // 按小时,此处一天内的小时数不会重复,因此可以直接按小时取
                // pv uv
                $result = Visit::where('resource_type', $resource_type)->where('resource_id', $course_id)->whereBetween('visit_time', $daterange['timespan'])->field(['SUM(pv)' => 'tp_pv', 'COUNT(DISTINCT ip)' => 'tp_uv', 'FROM_UNIXTIME(`visit_time`,"%H:00")' => 'date'])->group('date')->select()->toArray();
                $rows   = $this->parseRows($result, $rows);
            } else {
                // 按天 pv uv
                $result = Visit::where('resource_type', $resource_type)->where('resource_id', $course_id)->whereBetween('visit_time', $daterange['timespan'])->field(['SUM(pv)' => 'tp_pv', 'COUNT(DISTINCT ip)' => 'tp_uv', 'FROM_UNIXTIME(`visit_time`,"%Y-%m-%d")' => 'date'])->group('date')->select()->toArray();
                $rows   = $this->parseRows($result, $rows);
            }

            $data['rows']     = $rows;
            $data['timespan'] = $daterange['timespan'];
        }

        return $this->parseToData($data, 1, '获取成功');

    }

    /**
     * 解析统计图指标数据
     * Author   Martinsun<syh@sunyonghong.com>
     * Date 2020-07-21
     * @param    [type]                         $datas [description]
     * @param    [type]                         $rows  [description]
     * @return   [type]                                [description]
     */
    protected function parseRows($datas, $rows = [])
    {
        $result = array_column($datas, null, 'date');

        foreach ($rows as $key => $row) {
            $time = $row['date'];
            $pv   = $uv   = 0;
            if (isset($result[$time])) {
                $pv = $result[$time]['tp_pv'];
                $uv = $result[$time]['tp_uv'];
            }

            $rows[$key]['pv'] = $pv;
            $rows[$key]['uv'] = $uv;

        }

        return $rows;
    }
}
